/**
 * 
 */
package com.rnit.jexcel.neg;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Hashtable;
import java.util.Map;

import com.rnit.jexcel.bean.MarcaBean;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;


/**
 * @author rmsilva
 *
 */
public class CargaMarca {

	/**
	 * 
	 */
	public CargaMarca() {
		// TODO Auto-generated constructor stub
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub

		try 
		{ 
			
			System.out.println(" Lendo planilha . . .");
			
			Workbook workbook = Workbook.getWorkbook(new File("/workspace/JExcel/src/com/rnit/jexcel/neg/tabelamolicar.xls")); 
			//Workbook workbook = Workbook.getWorkbook(new File("/eclipseIndigo/workspace/JExcel/src/com/rnit/jexcel/neg/tabelamolicar.xls")); 
			
			Sheet sheet = workbook.getSheet(0); 
						
			MarcaBean MarcaCarregada = null; 
			
			for (int i=1; i<sheet.getRows(); i++) {
				
				Cell codigoTipoVeiculo = sheet.getCell(0,i);
				Cell codigoMarca       = sheet.getCell(2,i);
				Cell descricaoMarca    = sheet.getCell(3,i);
				
				if( codigoMarca.getContents() != null && descricaoMarca.getContents() != null && codigoTipoVeiculo.getContents() !=null ) {
					if(!codigoMarca.getContents().equals("") && !descricaoMarca.getContents().equals("") && !codigoTipoVeiculo.getContents().equals("")){
						
						if ((MarcaCarregada == null) || (!MarcaCarregada.getId().equalsIgnoreCase(codigoMarca.getContents()) 
												|| !MarcaCarregada.getIdTipo().equalsIgnoreCase(codigoTipoVeiculo.getContents()))) {
							MarcaBean marca = new MarcaBean();
							
							marca.setIdTipo(codigoTipoVeiculo.getContents());
							marca.setId(codigoMarca.getContents());
							marca.setDescricao(descricaoMarca.getContents());
							
							System.out.println(" gravando ...");
							System.out.println("");
							System.out.println("codigoTipoVeiculo ->" + codigoTipoVeiculo.getContents() );
							System.out.println("codigoMarca ->" + codigoMarca.getContents() );
							System.out.println("descricaoMarca ->" + descricaoMarca.getContents());
							System.out.println("");
							
							InsertMarca(marca);
							
							MarcaCarregada = new MarcaBean();
							MarcaCarregada.setDescricao(marca.getDescricao());
							MarcaCarregada.setId(marca.getId());
							MarcaCarregada.setIdTipo(marca.getIdTipo());
							
						}
						
					}
				}
				
			}
			
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}  catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void InsertMarca (MarcaBean marca) {
		
		
		Connection connection = null; 
		
		try {
			
			
			// Load the JDBC driver 
			String driverName = "com.mysql.jdbc.Driver"; // MySQL JDBC driver 
			Class.forName(driverName); 
		
			// Create a connection to the database 
			String serverName = "localhost:3306"; 
			String mydatabase = "cnpv?autoReconnect=true"; 
			String url = "jdbc:mysql://" + serverName + "/" + mydatabase; // a JDBC url 
			
			String username = "root"; 
			String password = "admin"; 
			
			connection = DriverManager.getConnection(url, username, password); 
			
			StringBuilder q = new StringBuilder();
			
			q.append("insert into marca (");
			q.append(" id_tipo_veiculo, ");
			q.append(" id, ");
			q.append(" ds_marca, ");
			q.append(" dt_incl, ");
			q.append(" id_usuario ");
			q.append(" )");
			q.append("values (?,?,?,?,1)");
			
			
			PreparedStatement ps = connection.prepareStatement(q.toString());

			ps.setString(1, marca.getIdTipo()); 
			ps.setString(2, marca.getId()); 
			ps.setString(3, marca.getDescricao());
			ps.setTimestamp(4, new Timestamp(new java.util.Date().getTime()));
			
			ps.executeUpdate();
			ps.close();
			
		} catch (Exception e) {
			e.printStackTrace();
			
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
		}
	}
}
